﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;

namespace SIPO.Library.DataAccess.Report
{
    public class rptPenerimaanRepo
    {
        private string connectionString;

        public rptPenerimaanRepo(string ConnectionString)
        {
            connectionString = ConnectionString;
        }

        public string PeriodeLaporan(string tglLaporan)
        {
            string result = string.Empty;

            Dictionary<string, string> dictionary = new Dictionary<string, string>();
            dictionary["01"] = "Januari";
            dictionary["02"] = "Februari";
            dictionary["03"] = "Maret";
            dictionary["04"] = "April";
            dictionary["05"] = "Mei";
            dictionary["06"] = "Juni";
            dictionary["07"] = "Juli";
            dictionary["08"] = "Agustus";
            dictionary["09"] = "September";
            dictionary["10"] = "Oktober";
            dictionary["11"] = "November";
            dictionary["12"] = "Desember";


            string[] splitTglLaporan = tglLaporan.Split('/');
            string year = splitTglLaporan[2];
            string month = dictionary[splitTglLaporan[1].ToString().Trim()];
            result = string.Format("{0} {1} {2}", splitTglLaporan[0], month, splitTglLaporan[2]);

            return result;
        }
        public DataTable GetPenerimaan(string parameters)
        {
            DataTable dt = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendFormat("    SELECT ");
                    sbQuery.AppendFormat(@"     ROW_NUMBER() over(order by recTransactionType.TransTypeName) as Row,
                                                DENSE_RANK() over(order by recTransactionType.TransTypeName) as RowFilter,
                                                recTransactionType.TransTypeID,
                                                recTransactionType.TransTypeName,
                                                recDebtor.DebtorID,recDebtor.DebtorName,
                                                recBilling.BillingNo,recBilling.ForPayment,
                                                recBilling.Value");
                    sbQuery.AppendFormat("    FROM ");
                    sbQuery.AppendFormat(@"     transactions.recBilling
                                                left join transactions.recInvoice on recBilling.InvoiceNo = recInvoice.InvoiceNo                                                
                                                inner join master.recDebtorSector on recDebtorSector.DebtorSectorID = recBilling.DebtorSectorID
                                                inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID
                                                inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID
                                                inner join master.recSector on recSector.SectorID = recDebtorSector.SectorID
                                                inner join master.recSubSector on recSubSector.SubSectorID = recDebtorSector.SubSectorID
                                                inner join master.recSubSubSector on recSubSubSector.SubSubSectorID = recDebtorSector.SubSubSectorID {0}", parameters);

                    cmd.CommandText = sbQuery.ToString();

                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;

                    try
                    {
                        conn.Open();
                        da.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }

                }
            }
            return dt;
        }

        public DataTable GetPenerimaan(string parameters, string parameterTerminDueDate,string sPeriodeAwal,string sPeriodeAkhir,string parametersRegistrasi)
        {
            DataTable dt = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {

                    StringBuilder sbQuery = new StringBuilder();
                    sbQuery.AppendLine("    SELECT ");
                    sbQuery.AppendLine(@"       ROW_NUMBER() over(order by recTransactionType.TransTypeName) as Row,
                                                DENSE_RANK() over(order by recTransactionType.TransTypeName) as RowFilter,
                                                recTransactionType.TransTypeID,
                                                recTransactionType.TransTypeName,
                                                recDebtor.DebtorID,recDebtor.DebtorName,
                                                recBilling.BillingNo,recBilling.ForPayment,
                                                recBilling.Value");
                    sbQuery.AppendLine("    FROM ");
                    sbQuery.AppendLine(@"       transactions.recBilling
                                                left join transactions.recInvoice on recBilling.InvoiceNo = recInvoice.InvoiceNo                                                
                                                inner join master.recDebtorSector on recDebtorSector.DebtorSectorID = recBilling.DebtorSectorID
                                                inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID
                                                inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID
                                                inner join master.recSector on recSector.SectorID = recDebtorSector.SectorID
                                                inner join master.recSubSector on recSubSector.SubSectorID = recDebtorSector.SubSectorID
                                                inner join master.recSubSubSector on recSubSubSector.SubSubSectorID = recDebtorSector.SubSubSectorID ");
                    sbQuery.AppendLine(parameters);
                    sbQuery.AppendLine(" AND recTransactionType.TransTypeCode = 2");
                    sbQuery.AppendLine(parameterTerminDueDate);
                    sbQuery.AppendLine(" UNION ");
                    sbQuery.AppendLine("    SELECT ");
                    sbQuery.AppendLine(@"       ROW_NUMBER() over(order by recTransactionType.TransTypeName) as Row,
                                                DENSE_RANK() over(order by recTransactionType.TransTypeName) as RowFilter,
                                                (CASE WHEN recTransactionType.TransTypeCode=10
                                                THEN (SELECT TransTypeID From master.recTransactionType WHERE recTransactionType.TransTypeCode=SUBSTRING(recBilling.prevBillingNo,1,1))
                                                WHEN recTransactionType.TransTypeCode=11
                                                THEN (SELECT TransTypeID From master.recTransactionType WHERE recTransactionType.TransTypeCode=SUBSTRING(recBilling.prevBillingNo,1,1))
                                                WHEN recTransactionType.TransTypeCode IN(5,6)
                                                THEN (SELECT TransTypeID From master.recTransactionType WHERE recTransactionType.TransTypeCode=2)
                                                ELSE recTransactionType.TransTypeID
                                                END) TransTypeID,

                                                (CASE WHEN recTransactionType.TransTypeCode=10
                                                THEN (SELECT TransTypeName From master.recTransactionType WHERE recTransactionType.TransTypeCode=SUBSTRING(recBilling.prevBillingNo,1,1))
                                                WHEN recTransactionType.TransTypeCode=11
                                                THEN (SELECT TransTypeName From master.recTransactionType WHERE recTransactionType.TransTypeCode=SUBSTRING(recBilling.prevBillingNo,1,1))
                                                WHEN recTransactionType.TransTypeCode IN(5,6)
                                                THEN (SELECT TransTypeName From master.recTransactionType WHERE recTransactionType.TransTypeCode=2)
                                                ELSE recTransactionType.TransTypeName
                                                END) TransTypeName,
                                                recDebtor.DebtorID,recDebtor.DebtorName,
                                                recBilling.BillingNo,recBilling.ForPayment,
                                                recBilling.Value");
                    sbQuery.AppendLine("    FROM ");
                    sbQuery.AppendLine(@"       transactions.recBilling
                                                left join transactions.recInvoice on recBilling.InvoiceNo = recInvoice.InvoiceNo                                                
                                                inner join master.recDebtorSector on recDebtorSector.DebtorSectorID = recBilling.DebtorSectorID
                                                inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID
                                                inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID
                                                inner join master.recSector on recSector.SectorID = recDebtorSector.SectorID
                                                inner join master.recSubSector on recSubSector.SubSectorID = recDebtorSector.SubSectorID
                                                inner join master.recSubSubSector on recSubSubSector.SubSubSectorID = recDebtorSector.SubSubSectorID");
                    sbQuery.AppendLine(parameters);
                    sbQuery.AppendLine(" AND recTransactionType.TransTypeCode <> 2");
                    sbQuery.AppendLine(" UNION ");
                    sbQuery.AppendLine(@"---REGISTRASI---
                                        SELECT ROW_NUMBER() over(order by recTransactionType.TransTypeName) as Row,
                                        DENSE_RANK() over(order by recTransactionType.TransTypeName) as RowFilter,
                                        recTransactionType.TransTypeID as TransTypeID,
                                        recTransactionType.TransTypeName as TransTypeName,
                                        0 as DebtorID, ' ' as DebtorName,
                                        recBilling.BillingNo,recBilling.ForPayment,
                                        recBilling.Value FROM transactions.recBilling
                                        inner join transactions.recInvoice on recBilling.InvoiceNo = recInvoice.InvoiceNo
                                        inner join transactions.recSectorRegistration on recInvoice.InvoiceNo = recSectorRegistration.InvoiceNo
                                        inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID
                                        inner join master.recSector on recSector.SectorID = recSectorRegistration.SectorID
                                        inner join master.recSubSector on recSubSector.SubSectorID = recSectorRegistration.SubSectorID
                                        inner join master.recSubSubSector on recSubSubSector.SubSubSectorID = recSectorRegistration.SubSubSectorID");
                    sbQuery.AppendLine(parametersRegistrasi);
                    sbQuery.AppendLine("AND recSectorRegistration.DebtorSectorID is null");
                    sbQuery.AppendLine(" UNION");
                    sbQuery.AppendLine(@" ---AKSI KORPORASI---
                                        SELECT ROW_NUMBER() over(order by recTransactionType.TransTypeName) as Row,
                                        DENSE_RANK() over(order by recTransactionType.TransTypeName) as RowFilter,
                                        recTransactionType.TransTypeID as TransTypeID,
                                        recTransactionType.TransTypeName as TransTypeName,
                                        0 as DebtorID, ' ' as DebtorName,
                                        recBilling.BillingNo,
                                        recBilling.ForPayment,
                                        recBilling.Value
                                        FROM transactions.recBilling
                                        inner join transactions.recInvoice on recBilling.InvoiceNo = recInvoice.InvoiceNo
                                        inner join transactions.recCorporateAction on recInvoice.InvoiceNo = recCorporateAction.InvoiceNo
                                        inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID
                                        where recInvoice.PaymentNo IS NULL");

                                       // and recBilling.BillingDate between '20131201' and '20131231' 
                    sbQuery.AppendFormat(" and recBilling.BillingDate between '{0}' and '{1}'", sPeriodeAwal, sPeriodeAkhir); 
                    sbQuery.AppendLine("AND recCorporateAction.DebtorSectorID is null");
                    
                    cmd.CommandText = sbQuery.ToString();

                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;

                    try
                    {
                        conn.Open();
                        da.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }

                }
            }
            return dt;
        }

        public DataTable GetPenerimaanDetail(string parameters, string parameterTerminDueDate, string sPeriodeAwal, string sPeriodeAkhir, string parametersRegistrasi,int DebtorID,int  transTypeCode)
        {
            DataTable dt = new DataTable();

            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = conn.CreateCommand())
                {

                    StringBuilder sbQuery = new StringBuilder();
                    if (DebtorID != 0)
                    {
                        sbQuery = new StringBuilder();
                        sbQuery.AppendLine("    SELECT ");
                        sbQuery.AppendLine(@"       ROW_NUMBER() over(order by recTransactionType.TransTypeName) as Row,
                                                DENSE_RANK() over(order by recTransactionType.TransTypeName) as RowFilter,
                                                recTransactionType.TransTypeID,
                                                recTransactionType.TransTypeName,
                                                recDebtor.DebtorID,recDebtor.DebtorName,
                                                recBilling.BillingNo,recBilling.ForPayment,
                                                recBilling.Value");
                        sbQuery.AppendLine("    FROM ");
                        sbQuery.AppendLine(@"       transactions.recBilling
                                                left join transactions.recInvoice on recBilling.InvoiceNo = recInvoice.InvoiceNo                                                
                                                inner join master.recDebtorSector on recDebtorSector.DebtorSectorID = recBilling.DebtorSectorID
                                                inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID
                                                inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID
                                                inner join master.recSector on recSector.SectorID = recDebtorSector.SectorID
                                                inner join master.recSubSector on recSubSector.SubSectorID = recDebtorSector.SubSectorID
                                                inner join master.recSubSubSector on recSubSubSector.SubSubSectorID = recDebtorSector.SubSubSectorID ");
                        sbQuery.AppendLine(parameters);
                        sbQuery.AppendLine(" AND recTransactionType.TransTypeCode = 2");
                        sbQuery.AppendLine(parameterTerminDueDate);
                        sbQuery.AppendLine(" UNION ");
                        sbQuery.AppendLine("    SELECT ");
                        sbQuery.AppendLine(@"       ROW_NUMBER() over(order by recTransactionType.TransTypeName) as Row,
                                                DENSE_RANK() over(order by recTransactionType.TransTypeName) as RowFilter,
                                                (CASE WHEN recTransactionType.TransTypeCode=10
                                                THEN (SELECT TransTypeID From master.recTransactionType WHERE recTransactionType.TransTypeCode=SUBSTRING(recBilling.prevBillingNo,1,1))
                                                WHEN recTransactionType.TransTypeCode=11
                                                THEN (SELECT TransTypeID From master.recTransactionType WHERE recTransactionType.TransTypeCode=SUBSTRING(recBilling.prevBillingNo,1,1))
                                                WHEN recTransactionType.TransTypeCode IN(5,6)
                                                THEN (SELECT TransTypeID From master.recTransactionType WHERE recTransactionType.TransTypeCode=2)
                                                ELSE recTransactionType.TransTypeID
                                                END) TransTypeID,

                                                (CASE WHEN recTransactionType.TransTypeCode=10
                                                THEN (SELECT TransTypeName From master.recTransactionType WHERE recTransactionType.TransTypeCode=SUBSTRING(recBilling.prevBillingNo,1,1))
                                                WHEN recTransactionType.TransTypeCode=11
                                                THEN (SELECT TransTypeName From master.recTransactionType WHERE recTransactionType.TransTypeCode=SUBSTRING(recBilling.prevBillingNo,1,1))
                                                WHEN recTransactionType.TransTypeCode IN(5,6)
                                                THEN (SELECT TransTypeName From master.recTransactionType WHERE recTransactionType.TransTypeCode=2)
                                                ELSE recTransactionType.TransTypeName
                                                END) TransTypeName,
                                                recDebtor.DebtorID,recDebtor.DebtorName,
                                                recBilling.BillingNo,recBilling.ForPayment,
                                                recBilling.Value");
                        sbQuery.AppendLine("    FROM ");
                        sbQuery.AppendLine(@"       transactions.recBilling
                                                left join transactions.recInvoice on recBilling.InvoiceNo = recInvoice.InvoiceNo                                                
                                                inner join master.recDebtorSector on recDebtorSector.DebtorSectorID = recBilling.DebtorSectorID
                                                inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID
                                                inner join master.recDebtor on recDebtor.DebtorID = recDebtorSector.DebtorID
                                                inner join master.recSector on recSector.SectorID = recDebtorSector.SectorID
                                                inner join master.recSubSector on recSubSector.SubSectorID = recDebtorSector.SubSectorID
                                                inner join master.recSubSubSector on recSubSubSector.SubSubSectorID = recDebtorSector.SubSubSectorID");
                        sbQuery.AppendLine(parameters);
                        sbQuery.AppendLine(" AND recTransactionType.TransTypeCode <> 2");
                    }
                    else
                    {
                        

                        //sbQuery.AppendLine(" UNION ");
                        if (transTypeCode == 1)
                        {
                            sbQuery = new StringBuilder();
                            sbQuery.AppendLine(@"---REGISTRASI---
                                        SELECT ROW_NUMBER() over(order by recTransactionType.TransTypeName) as Row,
                                        DENSE_RANK() over(order by recTransactionType.TransTypeName) as RowFilter,
                                        recTransactionType.TransTypeID as TransTypeID,
                                        recTransactionType.TransTypeName as TransTypeName,
                                        0 as DebtorID, ' ' as DebtorName,
                                        recBilling.BillingNo,recBilling.ForPayment,
                                        recBilling.Value FROM transactions.recBilling
                                        inner join transactions.recInvoice on recBilling.InvoiceNo = recInvoice.InvoiceNo
                                        inner join transactions.recSectorRegistration on recInvoice.InvoiceNo = recSectorRegistration.InvoiceNo
                                        inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID
                                        inner join master.recSector on recSector.SectorID = recSectorRegistration.SectorID
                                        inner join master.recSubSector on recSubSector.SubSectorID = recSectorRegistration.SubSectorID
                                        inner join master.recSubSubSector on recSubSubSector.SubSubSectorID = recSectorRegistration.SubSubSectorID");
                            sbQuery.AppendLine(parametersRegistrasi);
                            sbQuery.AppendLine("AND recSectorRegistration.DebtorSectorID is null");
                        }

                        else
                        {
                            sbQuery = new StringBuilder();
                            sbQuery.AppendLine(@" ---AKSI KORPORASI---
                                        SELECT ROW_NUMBER() over(order by recTransactionType.TransTypeName) as Row,
                                        DENSE_RANK() over(order by recTransactionType.TransTypeName) as RowFilter,
                                        recTransactionType.TransTypeID as TransTypeID,
                                        recTransactionType.TransTypeName as TransTypeName,
                                        0 as DebtorID, ' ' as DebtorName,
                                        recBilling.BillingNo,
                                        recBilling.ForPayment,
                                        recBilling.Value
                                        FROM transactions.recBilling
                                        inner join transactions.recInvoice on recBilling.InvoiceNo = recInvoice.InvoiceNo
                                        inner join transactions.recCorporateAction on recInvoice.InvoiceNo = recCorporateAction.InvoiceNo
                                        inner join master.recTransactionType on recTransactionType.TransTypeID = recBilling.TransTypeID
                                        where recInvoice.PaymentNo IS NULL");

                            sbQuery.AppendFormat(" and recBilling.BillingDate between '{0}' and '{1}'", sPeriodeAwal, sPeriodeAkhir);
                            sbQuery.AppendLine("AND recCorporateAction.DebtorSectorID is null");
                        }
                    }
                    cmd.CommandText = sbQuery.ToString();

                    SqlDataAdapter da = new SqlDataAdapter();
                    da.SelectCommand = cmd;

                    try
                    {
                        conn.Open();
                        da.Fill(dt);
                    }
                    catch (Exception ex)
                    {
                        throw (ex);
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }

                }
            }
            return dt;
        }
    }
}
